package com.qf.dao;

import com.qf.entity.Emp;
import com.qf.entity.EmpManager;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zdl
 * @date 2025/1/16
 */
public class EmpDao {
    public List<Emp> selectAllEmp(int pageNo, int pageSize, String queryName, String querySex) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1?useSSL=false", "root", "123456");
            QueryRunner qr = new QueryRunner();
            List<Object> paramList = new ArrayList<>();
            String sql = "select * from emp  where 1= 1";
            if (queryName != null) {
                sql += " and name like ?";
                paramList.add("%" + queryName + "%");
            }
            if (querySex != null ) {
                sql += " and sex = ?";
                paramList.add(querySex);
            }
            sql += " limit ?,?";
            paramList.add(pageNo);
            paramList.add(pageSize);
            /*Object[] params = {
                    (queryName != null && !queryName.isEmpty()) ? "%" + queryName + "%" : null,
                    (querySex != null && !querySex.isEmpty()) ? querySex : null,
                    pageNo, pageSize
            };*/
            Object[] params = paramList.toArray();
            System.out.println("最终拼成的sql：" + sql);
            List<Emp> query = qr.query(conn, sql, new BeanListHandler<>(Emp.class), params);
            return query;
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public int insertEmp(Emp emp) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1?useSSL=false", "root", "123456");
            QueryRunner qr = new QueryRunner();
            String sql = "insert into emp (name,age,sex,salary) values(?,?,?,?)";
            Object[] params = {emp.getName(), emp.getAge(), emp.getSex(), emp.getSalary()};
            int i = qr.update(conn, sql, params);
            return i;
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public int delEmp(Integer id) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1?useSSL=false", "root", "123456");
            QueryRunner qr = new QueryRunner();
            String sql = "delete from emp where id = ? ";
            int i = qr.update(conn, sql, id);
            return i;
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public Emp selectEmp(Integer id) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1?useSSL=false", "root", "123456");
            QueryRunner qr = new QueryRunner();
            String sql = "select * from emp where id  = ? ";
            Emp query = qr.query(conn, sql, new BeanHandler<>(Emp.class),id);
            return query;
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public Long selectEmpCount(String queryName,String querySex) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1?useSSL=false", "root", "123456");
            QueryRunner qr = new QueryRunner();
            String sql = "select count(*) from emp where 1 = 1";
            List<Object> paramList = new ArrayList<>();
            if (queryName != null) {
                sql += " and name like ?";
                paramList.add("%" + queryName + "%");
            }
            if (querySex != null) {
                sql += " and sex = ?";
                paramList.add(querySex);
            }
            System.out.println("查询总条数sql："+sql);
            Object[] params = paramList.toArray();
            Long query = qr.query(conn, sql, new ScalarHandler<>(),params);
            return query;
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public int updateEmp(Emp emp){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1?useSSL=false", "root", "123456");
            QueryRunner qr = new QueryRunner();
            String sql = "update emp set name = ? , age = ?, sex = ?, salary = ? where id = ? ";
            Object[] params = {emp.getName(),emp.getAge(),emp.getSex(),emp.getSalary(),emp.getId()};
            int i = qr.update(conn, sql, params);
            return i;
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
